from datetime import datetime
from hashlib import sha256
from urllib.parse import urlencode
import hmac, requests
from pandas._libs import json
import pymysql
import math
import psycopg2


CICC_BASE_API_URL = 'https://www.research.cicc.com/api'
APP_ID = 'app/api@thfund.com.cn'
APP_SECRET = 'aidwtw4uove3nxx1khsepwjmtejfb5vc'


# 获取token
def request_cicc_access_token():
    url = f'{CICC_BASE_API_URL}/oauth2.0/accessToken'
    data = {
        "grant_type": "client_credentials",
        "client_id": APP_ID,
        "client_secret": APP_SECRET,
    }
    response = requests.post(url,
                             headers={'Content-Type': 'application/x-www-form-urlencoded'},
                             data=data,
                             stream=False,
                             timeout=10,
                             )
    try:
        result = response.json()
        assert 'code' in result and result['code'] == 0, f'Failed to request access token:{response}'
        return result['data']['accessToken']
    except ValueError as e:
        print(f'\n\n>>>>>>>>>> ERROR <<<<<<<<<<\n\nInvalid response: {response.text}')
        raise e


# 获取秘钥调用接口
def request_cicc_data(method, path, params, body=None, content_type='application/json'):
    # 请根据实际情况进行缓存，2 小时有效
    access_token = request_cicc_access_token()

    params.update({'timestamp': str(int(datetime.now().timestamp() * 1000))})
    sorted_params = urlencode(dict(sorted(params.items())), safe='/@')
    body_str = '' if body is None else json.dumps(body)
    body_hash = sha256(body_str.encode('utf-8')).hexdigest()
    str_to_sign = "\n".join([method, path, sorted_params, body_hash])
    signature = hmac.new(APP_SECRET.encode('utf-8'), str_to_sign.encode('utf-8'),
                         digestmod=sha256).hexdigest()
    url = f'{CICC_BASE_API_URL}{path}'
    headers = {
        'Authorization': f'Bearer {access_token}',
        'X-CICC-Signature': signature,
        'Content-Type': 'application/json',
    }
    if method == 'GET':
        response = requests.get(url=url, params=params, headers=headers, data=
        body_str)
    elif method == 'POST':
        response = requests.post(url=url, params=params, headers=headers, data=
        body_str)
    else:
        assert False, f'Unsupported method {method}'
    try:
        data = response.json()
        # print(data)
        assert data['code'] == 0, f'Failed to request from {path}: {response.text}'
        return data
    except ValueError as e:
        print(f'\n\n>>>>>>>>>> ERROR <<<<<<<<<<\n\nInvalid response: {response.text}')
        raise e

# 调用指标定义接口
def request_cicc_ind_def(id):
    params = {}
    return request_cicc_data('GET', f'/data/indInfo/v1/'+str(id),params)
# 拉取指标数值
def request_cicc_indData_v2(index, currentPage ,pageSize):
    params = {}
    return request_cicc_data('GET', f'/data/indData/v2/'+index+'/'+str(currentPage)+'/'+str(pageSize), params)

# 增量同步指标
def sync_cicc_ind_data():
    # edbCodes='IND000000000074404,CICCIND000000000116219,IND000000000074402,IND000000000074405,CICCIND000000000050350,CICCIND000000000050351,CICCIND000000000050353,CICCIND000000000001431,CICCIND000000000001429,CICCIND000000000001250,CICCIND000000000001432,CICCIND000000000100390,CICCIND000000000100382,CICCIND000000000100379,CICCIND000000000050346,CICCIND000000000114212,CICCIND000000000114218'
    edbCodes=getParam(edbCodes)
    records=[]
    metas=[]
    edbCodesList = edbCodes.split(',')
    frequency_map = {
        "DAY": "日",
        "WEEK": "周",
        "MONTH": "月",
        "QUARTER": "季",
        "HALF_YEAR": "半年",
        "YEAR": "年"
    }
    try:
        for ciccIndex in edbCodesList:
            print("开始采集"+ciccIndex)
            # 指标定义
            json_data = request_cicc_ind_def(ciccIndex)
            meta=json_data["data"]
            metas.append({
                "edb_metrics_code": "CICC-"+meta["indDerId"],
                "third_code": meta["indDerId"],
                "third_type":"CICC",
                "metrics_name":  meta["indDerName"],
                "metrics_frequence": frequency_map[meta["indFrequency"]],
                "metrics_unit": meta["indUnit"],
                "metrics_startdate":  meta["indBeginDate"],
                "metrics_enddate":  meta["indEndDate"],
                "metrics_update":  meta["dataUpdateDate"],
                "metrics_data_source":  meta["dataSourceName"],
                "edb_catagory":  meta["category"],
                "metrics_remark":  meta["indDescription"],
                "metrics_nation": ''
            })

            pageNum = 1
            pageSize = 10000
            hasNextPage = True
            while hasNextPage == True:
                # 获取指标数值上新
                jsonData = request_cicc_indData_v2(ciccIndex,pageNum, pageSize)
                # 获取增量指标数据
                code = jsonData['code']
                if code==0:
                    indicatorIds = jsonData['data']
                    print("开始采集"+str(len(indicatorIds)))
                    for index, value in enumerate(indicatorIds):
                        indDerId=value["indDerId"]
                        indDerName=value["indDerName"]
                        indDerDate=value["indDerDate"]
                        indDerValue=value["indDerValue"]
                        isPred=value["isPred"]
                        publishTime=value["publishTime"]
                        removeFlag=value["removeFlag"]
                        records.append({
                            "keys": 'CICC-' +indDerId + indDerDate,  # 主键
                            "edb_metrics_code": 'CICC-' + indDerId,
                            "metrics_value": indDerValue,  # 值可以是任何数据类型
                            "data_time": indDerDate,  # 确保使用正确的键名和字符串值
                            "third_code": indDerId,
                            "third_type": "CICC"
                        })
                    if len(indicatorIds) < pageSize:
                        hasNextPage = False

                    else:
                        pageNum += 1

    except Exception as e:
         print(f'sync indicators error: {e}')
         raise e
    finally:
        print("数据获取完成")
    print("cicc指标值数据数据查询完成,查询条数:"+str(len(records)))
    # 使用executemany执行批量插入
    conn = psycopg2.connect(host="hgprecn-cn-v641lnkxm003-cn-shanghai.hologres.aliyuncs.com",
                            port=80,
                            dbname="odpstest",
                            user="LTAI5tQo9VtJ414iSEZrE8Vn",
                            password="BPBKqDiQ7JKPl6o3QDIFg6kh71nakS",
                            application_name="third_edb_data")
    # 使用executemany执行批量插入
    try:
        # 导入数据
        cur = conn.cursor()
        print("开始获取连接")
        # 初始化一个列表来存储批量插入的数据
        # 插入元数据
        insertlen=0;
        batch = []
        # 初始化一个计数器
        counter = 0;
        for data in metas:
            # 为每条记录创建一个元组，并添加到批量列表中
            batch.append((
                data['edb_metrics_code'],
                data['edb_catagory'],
                data['third_code'],
                data['third_type'],
                data['metrics_name'],
                data['metrics_unit'],
                data['metrics_frequence'],
                data['metrics_remark'],
                data['metrics_data_source'],
                data['metrics_startdate'],
                data['metrics_enddate'],
                data['metrics_update'],
                data['metrics_nation']
            ))
            # 每收集500条记录，执行一次插入操作
            if counter >= 500:
                # 构造批量插入的SQL语句
                values = ", ".join(["(%s, %s, %s, %s, %s,%s, %s,%s, %s, %s, %s, %s,%s,NUll)" for _ in range(len(batch))])
                sql = (f"INSERT INTO public.index_center_third_edb_metrics_main(edb_metrics_code, edb_catagory, third_code, third_type,metrics_name,"
                       f"metrics_unit,metrics_frequence,metrics_remark,metrics_data_source,metrics_startdate,metrics_enddate,metrics_update,metrics_nation,available_date) "
                       f"VALUES {values} ON CONFLICT (edb_metrics_code) DO UPDATE SET metrics_startdate = EXCLUDED.metrics_startdate,metrics_update = EXCLUDED.metrics_update,metrics_enddate = EXCLUDED.metrics_enddate,"
                       f"metrics_unit = EXCLUDED.metrics_unit,metrics_frequence = EXCLUDED.metrics_frequence,"
                       f"metrics_remark = EXCLUDED.metrics_remark,metrics_data_source = EXCLUDED.metrics_data_source, modify_time = now()")

                # 执行批量插入
                cur.execute(sql, [item for sublist in batch for item in sublist])
                # 提交事务
                conn.commit()
                insertlen+= 500
                # 重置计数器和批量列表
                counter = 0
                batch = []
                # 更新计数器
            counter += 1

        # 如果最后一批记录不足500条，也进行插入和提交
        if batch:
            values = ", ".join(["(%s, %s, %s, %s, %s,%s, %s,%s, %s, %s, %s, %s,%s,NUll)" for _ in range(len(batch))])
            sql = (f"INSERT INTO public.index_center_third_edb_metrics_main(edb_metrics_code, edb_catagory, third_code, third_type,metrics_name,"
                   f"metrics_unit,metrics_frequence,metrics_remark,metrics_data_source,metrics_startdate,metrics_enddate,metrics_update,metrics_nation,available_date) "
                   f"VALUES {values} ON CONFLICT (edb_metrics_code) DO UPDATE SET metrics_startdate = EXCLUDED.metrics_startdate,metrics_update = EXCLUDED.metrics_update,metrics_enddate = EXCLUDED.metrics_enddate,"
                   f"metrics_unit = EXCLUDED.metrics_unit,metrics_frequence = EXCLUDED.metrics_frequence,"
                   f"metrics_remark = EXCLUDED.metrics_remark,metrics_data_source = EXCLUDED.metrics_data_source, modify_time = now()")
            # 执行批量插入
            cur.execute(sql, [item for sublist in batch for item in sublist])
            # 提交事务
            conn.commit()
            insertlen+= len(batch)
        print("元数据数据写入完成,写入条数:"+str(insertlen))
        # 插入数据
        insertlen=0;
        batch = []
        # 初始化一个计数器
        counter = 0
        # 准备批量插入的数据
        for data in records:
            # 为每条记录创建一个元组，并添加到批量列表中
            value = float(data['metrics_value'])
            if not math.isnan(value):
                batch.append((
                    data['keys'],
                    data['edb_metrics_code'],
                    data['metrics_value'],
                    data['data_time'],
                    data['third_code'],
                    data['third_type']
                ))
            # 每收集500条记录，执行一次插入操作
            if counter >= 500:
                # 构造批量插入的SQL语句
                values = ", ".join(["(%s, %s, %s, %s, %s,%s)" for _ in range(len(batch))])
                sql = f"INSERT INTO public.index_center_third_edb_metrics_data(keys, edb_metrics_code, metrics_value, data_time,third_code,third_type) VALUES {values} ON CONFLICT (keys) DO UPDATE SET metrics_value = EXCLUDED.metrics_value, modify_time = now()"

                # 执行批量插入
                cur.execute(sql, [item for sublist in batch for item in sublist])
                # 提交事务
                conn.commit()
                insertlen+= 500
                # 重置计数器和批量列表
                counter = 0
                batch = []
                # 更新计数器
            counter += 1

        # 如果最后一批记录不足500条，也进行插入和提交
        if batch:
            values = ", ".join(["(%s, %s, %s, %s, %s,%s)" for _ in range(len(batch))])
            sql = f"INSERT INTO public.index_center_third_edb_metrics_data(keys, edb_metrics_code, metrics_value, data_time,third_code,third_type) VALUES {values} ON CONFLICT (keys) DO UPDATE SET metrics_value = EXCLUDED.metrics_value, modify_time = now()"
            # 执行批量插入
            cur.execute(sql, [item for sublist in batch for item in sublist])
            # 提交事务
            conn.commit()
            insertlen+= len(batch)

    except Exception as e:
        print("发生错误：", e)
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭游标和连接
        cur.close()
        conn.close()
    print("指标值数据数据写入完成")
    update_queries = []
    for data in metas:
        index_code = data['edb_metrics_code']
        end_date = data['metrics_enddate']
        update_query = f"""
        UPDATE data_browser.edb_index_center
        SET index_update_time = '{end_date}'
        WHERE index_code = '{index_code}'
        """
        update_queries.append(update_query)
    # 执行更新操作
    update_db('rds50g3807a68zwc9soo969.mysql.rds.aliyuncs.com', 'data_browser', 3306, 'D6a_T3_Brs', update_queries)
    print("Update completed successfully.")

def update_db(host, user, port, password, update_queries):
    conn = pymysql.connect(host=host, user=user, port=port, password=password, charset='utf8')
    try:
        c = conn.cursor()
        for query in update_queries:
            c.execute(query)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
if __name__ == '__main__':
    sync_cicc_ind_data()


